/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package hrbs.dao;


import java.sql.Statement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;


/**
 *
 * @author Oracle
 */
public final class DatabaseManager {
    private static String connectionString = "jdbc:oracle:thin:@10.0.2.15:1521:INSTA";
    private static String userString = "insta_user";
    private static String userPassword = "insta";

    private static volatile DatabaseManager instance = null;
    private Connection connexion;

    // Singleton
    public static DatabaseManager getInstance() {
        if(DatabaseManager.instance == null) {
            synchronized(DatabaseManager.class) {
                if(DatabaseManager.instance == null) {
                    DatabaseManager.instance = new DatabaseManager();
                }
            }
        }
        return instance;
    }

    public int setupDatabase() {
        String request = "SELECT count(*) as num_tables FROM user_tables WHERE table_name='CLIENT'";
        ResultSet res = this.select(request);
        int table_exists = 0;
        try {
            if(res.next()) {
                table_exists = res.getInt("num_tables");
                /*
                 * CONNECT TO DATABASE AND EXECUTE QUERIES
                 * */
                return table_exists;
            }
        } catch (Exception e) {
                // Log error ?
                return table_exists;
        }
        return table_exists;
    }


    public int getNewId(String strTableName, String strUniqueIdName) {
        String getMaxIdRequest = "SELECT MAX("+strUniqueIdName+") AS newId FROM "+strTableName;
        ResultSet resMaxIdResult = this.select(getMaxIdRequest);
        int newInsertId = 0;
        try {
            if(resMaxIdResult.next()) {
                newInsertId = resMaxIdResult.getInt("newId");
            } 
        } catch (Exception e) {
                // Log error ?
        }
        return newInsertId;
    }
            
    public ResultSet select(String strRequest) {
        try {
            Statement stmt = connexion.createStatement();
            ResultSet queryResult = stmt.executeQuery(strRequest);
            return queryResult;
        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
      }
            return null;
    }

    public String selectStringColumn(String strRequest, String strColumn) {
        String strResult = "";
        try {
            Statement stmt = connexion.createStatement();
            ResultSet queryResult = stmt.executeQuery(strRequest);
                 try {
                        queryResult.next();
                        strResult = queryResult.getString(strColumn);
                 } catch (Exception e) {
                        System.out.println(e);
                 }
        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
      }
      return strResult;
    }

    
    public int selectIntegerColumn(String strRequest, String strColumn) {
        int iResult = 0;
        try {
            Statement stmt = connexion.createStatement();
            ResultSet queryResult = stmt.executeQuery(strRequest);
                 try {
                        queryResult.next();
                        iResult = queryResult.getInt(strColumn);
                 } catch (Exception e) {
                        System.out.println(e);
                 }
        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
      }
      return iResult;
    }
    
    public boolean execute(String strRequest) {
        try {
            Statement stmt = connexion.createStatement();
            ResultSet res = stmt.executeQuery(strRequest);
            connexion.commit();
        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
       }
        return true;
    }

    private DatabaseManager() {
        try {
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
            DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver());
            this.connexion = DriverManager.getConnection(DatabaseManager.connectionString, DatabaseManager.userString, DatabaseManager.userPassword);
            DatabaseMetaData meta = connexion.getMetaData();
      } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
      }
    }
}
